/**
 * The database package of the application.
 */
package fr.umlv.m2.android.quizz.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;

/**
 * This class defines the database question object.
 * 
 * @author Gambouele Bertrand.
 * @author Viravout Alexandre.
 */
public class QuestionDb {
	/**
	 * The database version.
	 */
	private static final int DB_VERSION = 1;
	/**
	 * The database name.
	 */
	private static final String DB_NAME = "quizz.db";

	/**
	 * The question table name.
	 */
	private static final String QUESTION_TABLE = "question";
	/**
	 * The question id column name.
	 */
	private static final String ID = "id";
	/**
	 * The question id column number.
	 */
	private static final int ID_COL_NUM = 0;
	/**
	 * The question text column name.
	 */
	private static final String QUESTON_TEXT = "questionText";
	/**
	 * The question text column number.
	 */
	private static final int QUESTION_TEXT_COL_NUM = 1;
	/**
	 * The question answer id column name.
	 */
	private static final String ANSWER_ID = "answerId";
	/**
	 * The question answer id column number.
	 */
	private static final int ANSWER_ID_COL_NUM = 2;
	/**
	 * The question level id column name.
	 */
	private static final String LEVEL_ID = "levelId";
	/**
	 * The question level id column number.
	 */
	private static final int LEVEL_ID_COL_NUM = 3;
	/**
	 * The question theme id column name.
	 */
	private static final String THEME_ID = "themeId";
	/**
	 * The question theme id column number.
	 */
	private static final int THEME_ID_COL_NUM = 4;

	/**
	 * The database of the application.
	 */
	private SQLiteDatabase db;
	/**
	 * The database open helper.
	 */
	private final QuizzDatabaseOpenHelper quizzSQLite;
	
	/**
	 * Create the database question object.
	 *  
	 * @param context the context.
	 */
	public QuestionDb(Context context) {
		this.quizzSQLite = new QuizzDatabaseOpenHelper(context, DB_NAME, null, DB_VERSION);
	}

	/**
	 * Opens the database.
	 */
	public void open() {
		this.db = this.quizzSQLite.getWritableDatabase();
	}

	/**
	 * Closes the database.
	 */
	public void close() {
		this.db.close();
	}

	/**
	 * Gets the database.
	 * 
	 * @return the database.
	 */
	public SQLiteDatabase getDb() {
		return this.db;
	}
	
	/**
	 * Gets the number of question in the database.
	 * 
	 * @return the number of question.
	 */
	public long getNbQuestion() {
		return DatabaseUtils.queryNumEntries(this.db, QUESTION_TABLE);
	}
	
	/**
	 * Gets the number of question according a level id and a theme id.
	 * 
	 * @return the number of question.
	 */
	public int getNbQuestion(long levelId, long themeId) {		
		Cursor cursor = this.db.query(
				QUESTION_TABLE, 
				new String[] { ID }, 
				LEVEL_ID + " = " + levelId + " and " + THEME_ID + " = " + themeId,
				null, null, null, null
		);
		int count = cursor.getCount();
		cursor.close();
		
		return count;
	}
	
	/**
	 * Clear the question table.
	 */
	public void clear() {
		this.quizzSQLite.clearTable(this.db, QUESTION_TABLE);
	}

	/**
	 * Inserts a question in the database.
	 * 
	 * @param question the question to insert.
	 * @return the row ID of the newly inserted row, or -1 if an error occurred.
	 */
	public long insertQuestion(Question question) {
		ContentValues values = new ContentValues();

		values.put(QUESTON_TEXT, question.getQuestionText());
		values.put(ANSWER_ID, question.getAnswerId());
		values.put(LEVEL_ID, question.getLevelId());
		values.put(THEME_ID, question.getThemeId());

		return this.db.insert(QUESTION_TABLE, null, values);
	}
 
	/**
	 * Updates question data according his id.
	 * 
	 * @param id the question id.
	 * @param question the new question object.
	 * @return the number of rows affected.
	 */
	public int updateQuestion(long id, Question question) {
		ContentValues values = new ContentValues();
		
		values.put(QUESTON_TEXT, question.getQuestionText());
		values.put(ANSWER_ID, question.getAnswerId());
		values.put(LEVEL_ID, question.getLevelId());
		values.put(THEME_ID, question.getThemeId());
		
		return this.db.update(QUESTION_TABLE, values, ID + " = " + id, null);
	}
	
	private List<Question> cursorToQuestions(Cursor cursor) {
		if(cursor.getCount() == 0) {
			return null;
		} else {
			cursor.moveToFirst();
		}
		
		List<Question> questions = new ArrayList<Question>();
		Question question;
		do {
			question = new Question();
			question.setId(cursor.getInt(ID_COL_NUM));
			question.setQuestionText(cursor.getString(QUESTION_TEXT_COL_NUM));
			question.setAnswerId(cursor.getInt(ANSWER_ID_COL_NUM));
			question.setLevelId(cursor.getInt(LEVEL_ID_COL_NUM));
			question.setThemeId(cursor.getInt(THEME_ID_COL_NUM));
			questions.add(question);			
		} while(cursor.moveToNext());
		cursor.close();
		
		return questions;
	}
	
	/**
	 * Gets the questions according level id.
	 * 
	 * @param levelId the level id.
	 * @return the list of question.
	 */
	public List<Question> getQuestionsWithLevel(long levelId) {
		Cursor cursor = this.db.query(
				QUESTION_TABLE,
				new String[] { ID, QUESTON_TEXT, ANSWER_ID, LEVEL_ID, THEME_ID },
				LEVEL_ID + " = " + levelId,
				null, null, null, null
		);
		
		return this.cursorToQuestions(cursor);
	}
	
	/**
	 * Gets the map of questions according level id and theme id.
	 * 
	 * @param levelId the level id.
	 * @param themeId the theme id.
	 * @return the map of question.
	 */
	public List<Question> getQuestionsWithLevelAndTheme(long levelId, long themeId) {
		Cursor cursor = this.db.query(
				QUESTION_TABLE,
				new String[] { ID, QUESTON_TEXT, ANSWER_ID, LEVEL_ID, THEME_ID },
				LEVEL_ID + " = " + levelId + " and " + THEME_ID + " = " + themeId,
				null, null, null, null
		);
		
		return this.cursorToQuestions(cursor);
	}
}
